CREATE   procedure amsp_TreeMove
    @InSourceNodeID    numeric,
    @InDestNodeID    numeric
AS
BEGIN

  /*
  ** DESCRIPTION:
  ** ------------
  ** Moves a node in a Nav Menu tree (InSourceNodeID) to be a sibling directly
  ** beneath the destination (InDestNodeID) node.
  **
  ** INPUTS:
  ** -------
  **   @InSourceNodeID    Source node being moved
  **   @InDestNodeID    Destination node being moved beneath
  **
  ** OUTPUTS:
  ** -----------
  **   none
  **
  ** NOTES:
  ** ------
  **   none
  **
  ** HISTORY:
  ** --------
  ** 06/26/2001        N.Malhotra    Initial Version Created
  **
  */

  Declare  
    @HighSortOrder        numeric,
    @LowSortOrder        numeric,
    @SortIncrement        numeric,
    @NewSortOrder        numeric,
    @DestSortOrder        numeric,
    @DestDepth            numeric,
    @DestParentID        numeric,
    @DestAncestorID        numeric,
    @SourceSortOrder        numeric,
    @SourceDepth        numeric,
    @SourceParentID        numeric,
    @SourceAncestorID        numeric,
    @MaxRelativeSort        numeric,
    @TotalSortSpace        numeric,
    @Counter            int

  IF @InSourceNodeID != @InDestNodeID BEGIN

    BEGIN TRANSACTION

    /*
    ** Let's find out about our destination
    */

    SELECT
      @DestSortOrder = SortOrder,
      @DestDepth = CategoryDepth,
      @DestParentID = ParentNavMenuID,
      @DestAncestorID = AncestorNavMenuID
    FROM
      Nav_Menu
    WHERE
      NavMenuID = @InDestNodeID

    /*
    ** First, let's grab and save the source attributes for use in calculating relative moves later
    */

    SELECT
      @SourceSortOrder = SortOrder,
      @SourceParentID = ParentNavMenuID,
      @SourceAncestorID = AncestorNavMenuID,
      @SourceDepth = CategoryDepth
    FROM
      Nav_Menu
    WHERE
      NavMenuID = @InSourceNodeID

    IF @DestDepth > 1 BEGIN

      /*
      ** If the destination node has a depth > 1 then we simply set the
      ** Ancestor and Parent to the destination's Ancestor and Parent
      */
  
      UPDATE
        Nav_Menu
      SET
        ParentNavMenuID = @DestParentID,
        AncestorNavMenuID = @DestAncestorID,
        CategoryDepth = @DestDepth
      WHERE
        NavMenuID = @InSourceNodeID
    END
    ELSE BEGIN

      /*
      ** Otherwise, the destination node has a depth of 1 so both the
      ** Ancestor and Parent need to be set to the node itself
      */
  
      UPDATE
        Nav_Menu
      SET
        ParentNavMenuID = @InSourceNodeID,
        AncestorNavMenuID = @DestAncestorID,
        CategoryDepth = @DestDepth
      WHERE
        NavMenuID = @InSourceNodeID

    END

    /*
    ** Now, let's find the low and high end of our sort order range.
    */

    SELECT
      @HighSortOrder = IsNULL(MIN(SortOrder), 0)
    FROM
      Nav_Menu
    WHERE
      CategoryDepth <= @DestDepth AND
      SortOrder > @DestSortOrder

    IF @HighSortOrder = 0
      SELECT
        @HighSortOrder = MAX(SortOrder) + 10
      FROM
        Nav_Menu

    SELECT
      @LowSortOrder = MAX(SortOrder)
    FROM
      Nav_Menu
    WHERE
      SortOrder < @HighSortOrder

    /*
    ** OK, with our low point and high point established, we can
    ** easily establish a point in the middle.  It's somewhat arbitrary
    ** but we're going to insert in increments of 1/10 the distance between
    ** high and low because we know all decendants have to still be added.
    */

    SET @NewSortOrder = @LowSortOrder + .1 * (@HighSortOrder - @LowSortOrder)

    UPDATE
      Nav_Menu
    SET
      SortOrder = @NewSortOrder
    WHERE
      NavMenuID = @InSourceNodeID

    /*
    ** Now, sort of a funny way to do this, but it is fast and efficient ...
    ** Were going to throw all of the descendants into a temp table with only
    ** their NavMenuID and their SortOrder relative to the Source Node we're
    ** moving and likewise, relative CategoryDepth.  
    */

    create table #temp (NavMenuID numeric, RelativeSort numeric, RelativeDepth numeric)

    /*
    ** First insert the Source Node
    */

    INSERT INTO #temp VALUES (@InSourceNodeID, 0, 0)
  
    /*
    ** As long as there are new children, keep adding them to #temp.
    ** To make sure a bad nav_menu relationship doesn't lock the server,
    ** we also limit this to 50 iterations (i.e. 50 levels).
    */

    SET @Counter = 0

    WHILE @@rowCount > 0 and @Counter < 50 BEGIN
      SET @Counter = @Counter + 1
      INSERT INTO #temp (NavMenuID, RelativeSort, RelativeDepth)
      SELECT a.NavMenuID, a.SortOrder - @SourceSortOrder, a.CategoryDepth - @SourceDepth
        FROM Nav_Menu a, #temp b
       WHERE a.ParentNavMenuID = b.NavMenuID
         and a.NavMenuID not in (SELECT NavMenuID FROM #temp)
    END

    /*
    ** Now that our table table has all descendants, a single update will
    ** set their values.  Only SortOrder is tricky, and we use the relative
    ** offset to compute this.
    */

    SELECT @MaxRelativeSort = MAX(RelativeSort) from #temp
    SET @TotalSortSpace = @HighSortOrder - @NewSortOrder
  
    UPDATE Nav_Menu
       SET AncestorNavMenuID = @SourceAncestorID,
           CategoryDepth = (select @DestDepth + t.RelativeDepth
                              from #temp t
                             where t.NavMenuID = NavMenu.NavMenuID),
           SortOrder = (select @NewSortOrder + ((t.RelativeSort / @MaxRelativeSort) * @TotalSortSpace)
                          from #temp t
                         where t.NavMenuID = NavMenu.NavMenuID)
     WHERE
           NavMenuID in (select NavMenuID from #temp) AND
           NavMenuID != @InSourceNodeID
        
    COMMIT TRANSACTION

  END

END

GO
GRANT EXECUTE ON  [dbo].[amsp_TreeMove] TO [IMIS]
GO